﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//Please add references
namespace CustomsSystem.DAL
{
    /// <summary>
    /// 数据访问类:PayService
    /// </summary>
    public partial class PayService
    {
        public PayService()
        { }
        #region  BasicMethod
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int payID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from T_pay");
            strSql.Append(" where payID=@payID");
            SqlParameter[] parameters = {
					new SqlParameter("@payID", SqlDbType.Int,4)
			};
            parameters[0].Value = payID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(CustomsSystem.Model.Pay model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_pay(");
            strSql.Append("sheetID,cID,payTypeID,feeTypeID,sfeeAmount,empID,payDate,payType,payBank,remark,inputDate,afeeAmount,billID,invNO )");
            strSql.Append(" values (");
            strSql.Append("@sheetID,@cID,@payTypeID,@feeTypeID,@sfeeAmount,@empID,@payDate,@payType,@payBank,@remark,@inputDate,@afeeAmount,@billID,@invNO )");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@sheetID", SqlDbType.Int,4),
					new SqlParameter("@cID", SqlDbType.Int,4),
					new SqlParameter("@payTypeID", SqlDbType.Int,4),
					new SqlParameter("@feeTypeID", SqlDbType.VarChar,6),
					new SqlParameter("@sfeeAmount", SqlDbType.Decimal,5),
					new SqlParameter("@empID", SqlDbType.Char,4),
					new SqlParameter("@payDate", SqlDbType.DateTime),
					new SqlParameter("@payType", SqlDbType.Int,4),
					new SqlParameter("@payBank", SqlDbType.VarChar,100),
					new SqlParameter("@remark", SqlDbType.VarChar,500),
					new SqlParameter("@inputDate", SqlDbType.DateTime),
					new SqlParameter("@afeeAmount", SqlDbType.Decimal,5),
                    new SqlParameter("@invNO", SqlDbType.VarChar ,30),
					new SqlParameter("@billID", SqlDbType.Int,4)};
            parameters[0].Value = model.sheetID;
            parameters[1].Value = model.cID;
            parameters[2].Value = model.payTypeID;
            parameters[3].Value = model.feeTypeID;
            parameters[4].Value = model.sfeeAmount;
            parameters[5].Value = model.empID;
            parameters[6].Value = model.payDate;
            parameters[7].Value = model.payType;
            parameters[8].Value = model.payBank;
            parameters[9].Value = model.remark;
            parameters[10].Value = model.inputDate;
            parameters[11].Value = model.afeeAmount;
            parameters[12].Value = model.InvNO;
            parameters[13].Value = model.billID;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(CustomsSystem.Model.Pay model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_pay set ");
            strSql.Append("sheetID=@sheetID,");
            strSql.Append("cID=@cID,");
            strSql.Append("payTypeID=@payTypeID,");
            strSql.Append("feeTypeID=@feeTypeID,");
            strSql.Append("sfeeAmount=@sfeeAmount,");
            strSql.Append("empID=@empID,");
            strSql.Append("payDate=@payDate,");
            strSql.Append("payType=@payType,");
            strSql.Append("payBank=@payBank,");
            strSql.Append("remark=@remark,");
            strSql.Append("inputDate=@inputDate,");
            strSql.Append("afeeAmount=@afeeAmount,");
            strSql.Append("billID=@billID");
            strSql.Append("invNO=@invNO");
            strSql.Append(" where payID=@payID");
            SqlParameter[] parameters = {
					new SqlParameter("@sheetID", SqlDbType.Int,4),
					new SqlParameter("@cID", SqlDbType.Int,4),
					new SqlParameter("@payTypeID", SqlDbType.Int,4),
					new SqlParameter("@feeTypeID", SqlDbType.VarChar,6),
					new SqlParameter("@sfeeAmount", SqlDbType.Decimal,5),
					new SqlParameter("@empID", SqlDbType.Char,4),
					new SqlParameter("@payDate", SqlDbType.DateTime),
					new SqlParameter("@payType", SqlDbType.Int,4),
					new SqlParameter("@payBank", SqlDbType.VarChar,100),
					new SqlParameter("@remark", SqlDbType.VarChar,500),
					new SqlParameter("@inputDate", SqlDbType.DateTime),
					new SqlParameter("@afeeAmount", SqlDbType.Decimal,5),
					new SqlParameter("@billID", SqlDbType.Int,4),
                    new SqlParameter("@invNO", SqlDbType.VarChar ,30),
					new SqlParameter("@payID", SqlDbType.Int,4)};
            parameters[0].Value = model.sheetID;
            parameters[1].Value = model.cID;
            parameters[2].Value = model.payTypeID;
            parameters[3].Value = model.feeTypeID;
            parameters[4].Value = model.sfeeAmount;
            parameters[5].Value = model.empID;
            parameters[6].Value = model.payDate;
            parameters[7].Value = model.payType;
            parameters[8].Value = model.payBank;
            parameters[9].Value = model.remark;
            parameters[10].Value = model.inputDate;
            parameters[11].Value = model.afeeAmount;
            parameters[12].Value = model.billID;
            parameters[13].Value = model.payID;
            parameters[14].Value = model.InvNO;
       
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int payID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_pay ");
            strSql.Append(" where payID=@payID");
            SqlParameter[] parameters = {
					new SqlParameter("@payID", SqlDbType.Int,4)
			};
            parameters[0].Value = payID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string payIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_pay ");
            strSql.Append(" where payID in (" + payIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int sheetID, int payTypeID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_pay ");
            strSql.Append(" where sheetID=" + sheetID + "");
            strSql.Append("and payTypeID=" + payTypeID + "");
            SqlParameter[] parameters = {
					new SqlParameter("@sheetID", SqlDbType.Int,4),
                    new SqlParameter("@payTypeID", SqlDbType.Int,4)
			};
            parameters[0].Value = sheetID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetInfofromPay(int sheetID, int payTypeID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select *");
            strSql.Append(" FROM T_pay ");

            strSql.Append(" where sheetID = " + sheetID + "");
            strSql.Append("and payTypeID=" + payTypeID + " and feeTypeID!='0'");
            
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CustomsSystem.Model.Pay GetModel(int payID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 payID,sheetID,cID,payTypeID,feeTypeID,sfeeAmount,empID,payDate,payType,payBank,remark,invNO,inputDate,afeeAmount,billID from T_pay ");
            strSql.Append(" where payID=@payID");
            SqlParameter[] parameters = {
					new SqlParameter("@payID", SqlDbType.Int,4)
			};
            parameters[0].Value = payID;

            CustomsSystem.Model.Pay model = new CustomsSystem.Model.Pay();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CustomsSystem.Model.Pay DataRowToModel(DataRow row)
        {
            CustomsSystem.Model.Pay model = new CustomsSystem.Model.Pay();
            if (row != null)
            {
                if (row["payID"] != null && row["payID"].ToString() != "")
                {
                    model.payID = int.Parse(row["payID"].ToString());
                }
                if (row["sheetID"] != null && row["sheetID"].ToString() != "")
                {
                    model.sheetID = int.Parse(row["sheetID"].ToString());
                }
                if (row["cID"] != null && row["cID"].ToString() != "")
                {
                    model.cID = int.Parse(row["cID"].ToString());
                }
                if (row["payTypeID"] != null && row["payTypeID"].ToString() != "")
                {
                    model.payTypeID = int.Parse(row["payTypeID"].ToString());
                }
                if (row["feeTypeID"] != null)
                {
                    model.feeTypeID = row["feeTypeID"].ToString();
                }
                if (row["sfeeAmount"] != null && row["sfeeAmount"].ToString() != "")
                {
                    model.sfeeAmount = decimal.Parse(row["sfeeAmount"].ToString());
                }
                if (row["empID"] != null)
                {
                    model.empID = row["empID"].ToString();
                }
                if (row["payDate"] != null && row["payDate"].ToString() != "")
                {
                    model.payDate = DateTime.Parse(row["payDate"].ToString());
                }
                if (row["payType"] != null && row["payType"].ToString() != "")
                {
                    model.payType = int.Parse(row["payType"].ToString());
                }
                if (row["payBank"] != null)
                {
                    model.payBank = row["payBank"].ToString();
                }
                if (row["remark"] != null)
                {
                    model.remark = row["remark"].ToString();
                }
                if (row["inputDate"] != null && row["inputDate"].ToString() != "")
                {
                    model.inputDate = DateTime.Parse(row["inputDate"].ToString());
                }
                if (row["afeeAmount"] != null && row["afeeAmount"].ToString() != "")
                {
                    model.afeeAmount = decimal.Parse(row["afeeAmount"].ToString());
                }
                if (row["invNO"] != null && row["invNO"].ToString() != "")
                {
                    model.InvNO  = row["invNO"].ToString();
                }
                if (row["billID"] != null && row["billID"].ToString() != "")
                {
                    model.billID = int.Parse(row["billID"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果payTypeID（object）。
        /// </summary>
        public object GetPayTypeID(string SQLString)
        {
            int sheetID = int.Parse(SQLString);
            SQLString = "select PayTypeID from T_pay where sheetID = "+sheetID+"";
            return DbHelperSQL.GetSingle(SQLString);
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回销账类型的数量，2为应收应付都已销。
        /// </summary>
        public object GetPayTypeNum(string SQLString)
        {
            int sheetID = int.Parse(SQLString);
            SQLString = "select Count(distinct PayTypeID) from T_pay where sheetID = " + sheetID + "";
            return DbHelperSQL.GetSingle(SQLString);
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select payID,sheetID,cID,payTypeID,feeTypeID,sfeeAmount,empID,payDate,payType,payBank,remark,invNO,inputDate,afeeAmount,billID ");
            strSql.Append(" FROM T_pay ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" payID,sheetID,cID,payTypeID,feeTypeID,sfeeAmount,empID,payDate,payType,payBank,remark,invNO,inputDate,afeeAmount,billID ");
            strSql.Append(" FROM T_pay ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM T_pay ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.payID desc");
            }
            strSql.Append(")AS Row, T.*  from T_pay T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "T_pay";
            parameters[1].Value = "payID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
        #region  ExtensionMethod

        #endregion  ExtensionMethod
    }
}

